Understanding INCLUDE Expressions in Tableau

Topic: Tableau Level of Detail (LOD) Expressions | Focus: INCLUDE syntax, calculation granularity, aggregation, filters, practical examples, exercises, and interview questions.

Course: JustAcademy Tableau Training | Register for a Tableau Course Demo

1. Introduction to INCLUDE Expressions

An INCLUDE expression is a type of Level of Detail (LOD) expression in Tableau. It adds one or more specified dimensions to the dimensions already present in the visualization when calculating a measure. Tableau then aggregates the more detailed results to the level displayed in the view.

INCLUDE is useful when you need to calculate values at a finer level of detail than the visualization itself. For example, you may have a chart showing sales by region, but you want to calculate the average sales per customer within each region. You can use INCLUDE to calculate sales for individual customers first and then average those customer-level totals by region.

Why do we need INCLUDE?

• To calculate customer-level or product-level metrics in a summary visualization.
• To calculate averages of subtotals, such as average customer sales by region.
• To calculate at a finer level of detail than the dimensions shown in the view.
• To compare total sales with average sales per customer.
• To analyze data at multiple levels of detail without changing the final chart's intended layout.

Key idea: INCLUDE adds the specified dimensions to the view's level of detail for the calculation. The result can then be aggregated back to the visualization's displayed level.

2. Syntax of an INCLUDE Expression

The general syntax is:

{ INCLUDE [Dimension] : AGGREGATE([Measure]) }

Example:

{ INCLUDE [Customer Name] : SUM([Sales]) }

This expression calculates sales for each customer at the underlying data level while retaining the dimensions already used in the view.

Understanding each component

The aggregate expression must use an aggregation such as SUM, AVG, MIN, MAX, COUNT, or COUNTD, as appropriate. Tableau does not support ATTR or table calculations inside the aggregate expression of an LOD calculation.

3. How INCLUDE Expressions Work

Consider a visualization with Region on Columns and Sales on Rows. The chart displays total sales for each region. Now suppose you want to know the average sales per customer in each region without adding Customer Name to the chart.

Create the following calculated field:

{ INCLUDE [Customer Name] : SUM([Sales]) }

This expression adds Customer Name to the view's existing dimensions for the calculation. Tableau first calculates the sales total for each customer within each region. You can then set the resulting measure's aggregation to AVG to display the average customer-level sales for each region.

Calculation process

1. The visualization contains Region.
2. INCLUDE adds Customer Name to the calculation's level of detail.
3. Tableau calculates SUM(Sales) for each customer within each region.
4. The calculated customer-level values are aggregated for the visualization.
5. Using AVG displays the average customer sales amount per region.

Important: The INCLUDE expression itself does not automatically mean that the final answer is an average. The aggregation applied to the resulting field in the visualization determines how the more detailed results are summarized.

4. Example Dataset: Sales by Region and Customer

Consider this simplified dataset:

There are multiple underlying records for Customer A. To calculate the average of customer totals, first calculate sales per customer.

Step 1: Calculate sales for each customer

Step 2: Calculate average customer sales by region

West:

(300 + 300) / 2 = 300

East:

(400 + 200) / 2 = 300

The average customer sales amount is 300 in both regions. This differs from simply dividing the total sales by the number of underlying records if customers have different numbers of records.

5. Creating an INCLUDE Expression in Tableau

1. Open Tableau Desktop or a Tableau environment that supports LOD expressions.
2. Connect to a dataset, such as Sample - Superstore.
3. Open a new worksheet.
4. From the Data pane, right-click and select Create Calculated Field, or use Analysis > Create Calculated Field.
5. Name the calculation Customer Sales Total.
6. Enter the following formula:

{ INCLUDE [Customer Name] : SUM([Sales]) }

7. Click OK to save the calculation.
8. Drag Region to Columns.
9. Drag Customer Sales Total to Rows.
10. Open the measure's menu on the shelf and change its aggregation to Average.
11. Compare the resulting average customer sales by region with the total sales by region.

Expected result: The view can display average customer-level sales by region while keeping Customer Name out of the visible chart dimensions.

6. INCLUDE With One Dimension

The simplest INCLUDE expressions add one dimension to the existing view level of detail.

Example 1: Sales per Customer

{ INCLUDE [Customer Name] : SUM([Sales]) }

Calculates sales totals per customer, along with the dimensions already present in the view.

Example 2: Profit per Product

{ INCLUDE [Product Name] : SUM([Profit]) }

Calculates profit for each product within the current view's level of detail. If the view displays Category, the product-level values can be aggregated by category.

Example 3: Order Count per Customer

{ INCLUDE [Customer Name] : COUNTD([Order ID]) }

Calculates the number of distinct orders for each customer within the existing view's dimensions. The resulting field can be aggregated to analyze order counts at a higher level.

Example 4: Average Discount per Product

{ INCLUDE [Product Name] : AVG([Discount]) }

Calculates average Discount at the product level alongside the dimensions already in the view. Consider the data grain carefully when interpreting this result.

7. INCLUDE With Multiple Dimensions

You can add multiple dimensions to the calculation by separating them with commas.

{ INCLUDE [Customer Name], [Product Name] : SUM([Sales]) }

This calculation adds both Customer Name and Product Name to the current view's level of detail for the calculation.

Practical example

Suppose the view displays Region and Category. You want to calculate sales for each customer-product combination within the applicable region and category.

{ INCLUDE [Customer Name], [Product Name] : SUM([Sales]) }

Tableau calculates at the more detailed combination of the view's dimensions, Customer Name, and Product Name. The resulting values can then be aggregated back to Region and Category for comparison.

Common applications

• Customer and product sales analysis.
• Product-level profit analysis within categories.
• Order counts by customer and region.
• Sales per salesperson within a department.
• Detailed transaction analysis summarized by month or region.

8. INCLUDE and View Granularity

Granularity refers to the level of detail represented by the data or by the marks in a visualization. An INCLUDE expression adds the declared dimension or dimensions to the view's existing level of detail.

INCLUDE calculations have the same level of detail as the view or a finer one. The result is aggregated to the view level, rather than repeating a coarser value across more detailed marks as can happen with FIXED expressions.

9. INCLUDE vs Normal Aggregation

A normal aggregate such as SUM(Sales) calculates at the visualization's current level of detail. An INCLUDE expression can calculate at a more detailed level first, allowing a different aggregation to be applied afterward.

Normal aggregation

SUM([Sales])

If Region is in the view, this returns total sales per region.

INCLUDE aggregation

{ INCLUDE [Customer Name] : SUM([Sales]) }

This creates customer-level sales totals within each region or other dimensions in the view. Setting the field's aggregation to AVG produces average customer-level sales at the view's level of detail.

10. INCLUDE vs FIXED vs EXCLUDE

Remember: Use INCLUDE when you need extra detail for a calculation but do not want to add those dimensions to the visualization itself.

11. INCLUDE and Filters

INCLUDE and EXCLUDE LOD expressions are evaluated after ordinary dimension filters. Therefore, dimension filters generally affect the records considered by an INCLUDE expression.

Example

Consider this expression:

{ INCLUDE [Customer Name] : SUM([Sales]) }

If you filter the view to show only the Technology category, the INCLUDE calculation generally uses the records that remain after the dimension filter is applied. The resulting customer sales totals therefore reflect the filtered data rather than automatically retaining all categories.

Filter behavior to remember

• Extract filters: Restrict the data included in the extract.
• Data source filters: Restrict the data available from the data source.
• Context filters: Restrict data before the LOD calculations.
• Dimension filters: Applied before INCLUDE and EXCLUDE calculations.
• Measure filters: Applied later in Tableau's order of operations.
• Table calculation filters: Applied after table calculations and generally hide marks rather than removing underlying records from earlier calculations.

Filter behavior can depend on the specific data model and calculation. When a result looks unexpected, test the expression with and without the filter and confirm which stage of Tableau's order of operations is involved.

12. Calculating Average Customer Sales by Region

This is one of the most common business uses of INCLUDE expressions.

Step 1: Create customer sales totals

{ INCLUDE [Customer Name] : SUM([Sales]) }

Name the field Customer Sales Total.

Step 2: Display average customer sales

1. Drag Region to Columns.
2. Drag Customer Sales Total to Rows.
3. Open the field's menu on Rows.
4. Select Measure and then Average.

The visualization now shows average sales per customer within each region, assuming Customer Name uniquely identifies each customer in the data.

Business value

• Compare customer spending across regions.
• Find regions with high average customer value.
• Identify opportunities for targeted marketing.
• Compare customer-level performance with overall regional sales.

13. Calculating Average Sales per State

INCLUDE can also calculate at State level while the visualization displays a higher-level dimension such as Category.

Calculated field

{ INCLUDE [State] : SUM([Sales]) }

This calculates sales totals for each State within the current view's dimensions. Set the resulting measure's aggregation to AVG to examine the average state sales total within each displayed group.

Example

Suppose a category contains three states with sales totals of 1,000, 2,000, and 3,000.

Average State Sales = (1000 + 2000 + 3000) / 3
                    = 2000

This is different from calculating average sales per transaction. The INCLUDE expression creates state-level totals, and AVG averages those state totals.

14. INCLUDE With Date Dimensions

You can use date dimensions in an INCLUDE expression to calculate values at a finer date granularity than the view displays.

Example: Daily Sales Totals

{ INCLUDE DATETRUNC('day', [Order Date]) : SUM([Sales]) }

This adds the order date truncated to the day level to the view's level of detail for the calculation. If the visualization displays Month, Tableau can calculate daily totals and then aggregate them to the month-level view.

Example: Average Daily Sales by Month

Create the daily sales expression above and set the resulting field's aggregation to AVG in a view grouped by Month. This can help show the average daily sales total within each displayed month.

Note: Check whether your date field contains timestamps and whether truncating it to the day level matches the intended business definition.

15. INCLUDE With COUNTD

INCLUDE can also be used with COUNTD to calculate distinct counts at a more detailed level.

Example: Distinct Orders per Customer

{ INCLUDE [Customer Name] : COUNTD([Order ID]) }

This calculates distinct Order ID counts for each customer alongside the dimensions in the view. You can use the resulting values to compare order frequency across regions or customer segments.

Example: Distinct Products per Customer

{ INCLUDE [Customer Name] : COUNTD([Product Name]) }

This calculates the distinct product count for each customer at the current view's level of detail plus Customer Name. When summarizing the results, choose an aggregation that matches the question—for example, average number of distinct products per customer.

16. Common Mistakes and Troubleshooting

Mistake 1: Assuming INCLUDE always calculates an average

Problem: You create an INCLUDE expression but see totals instead of averages.

Solution: Change the aggregation of the resulting field in the view to AVG if your goal is to average the more detailed results.

Mistake 2: Adding a dimension to the wrong level

Problem: The calculation returns values at an unexpected granularity.

Solution: Check the dimensions already present in the view and the additional dimensions declared in the INCLUDE expression.

Mistake 3: Confusing average customer sales with average transaction sales

Problem: The result differs from the average of the raw Sales field.

Solution: Remember that { INCLUDE [Customer Name] : SUM([Sales]) } first calculates customer-level totals. AVG then averages those totals; it does not simply average individual transaction values.

Mistake 4: Using customer names that are not unique

Problem: Two different customers with the same name are combined into one calculation group.

Solution: Use a unique Customer ID when possible.

Mistake 5: Assuming INCLUDE ignores dimension filters

Problem: A filtered result changes unexpectedly.

Solution: INCLUDE is evaluated after ordinary dimension filters, so the filter generally affects the records included in the calculation.

Mistake 6: Using the wrong aggregation after INCLUDE

Problem: A result is much higher than expected.

Solution: Confirm whether you want the sum, average, minimum, maximum, or count of the detailed results.

17. Best Practices for INCLUDE Expressions

• Start by identifying the level of detail of the current visualization.
• Specify only the dimensions required for the detailed calculation.
• Choose the final aggregation deliberately.
• Use a unique identifier when analyzing customers, products, or other entities.
• Test the calculation in a simple worksheet before adding it to a dashboard.
• Compare the results against a manually calculated sample to verify the output.
• Check how filters affect the records considered by the expression.
• Use clear names such as Customer Sales Total or Daily Sales Total.
• Document whether the metric represents a total per customer, average per customer, average per transaction, or another business definition.

18. Practical Exercise: Average Customer Sales by Region

Objective: Build a Tableau chart comparing total sales and average customer sales by region.

1. Connect to Sample - Superstore.
2. Create a calculated field named Customer Sales Total.
3. Enter this formula:

{ INCLUDE [Customer Name] : SUM([Sales]) }

4. Drag Region to Columns.
5. Drag SUM(Sales) to Rows to display total sales by region.
6. Drag Customer Sales Total to Rows as a second measure.
7. Change the aggregation of Customer Sales Total to AVG.
8. Compare total sales with average customer sales across regions.
9. Try adding Segment to the view and observe how the INCLUDE calculation is evaluated at the more detailed view level.
10. Apply a Category filter and observe how the INCLUDE result changes.

Expected learning outcome: You should understand how INCLUDE adds a dimension to the calculation and how the final aggregation affects the displayed result.

19. Practice Questions With Answers

Question 1: Calculate sales per customer.

{ INCLUDE [Customer Name] : SUM([Sales]) }

Question 2: Calculate profit per product.

{ INCLUDE [Product Name] : SUM([Profit]) }

Question 3: Calculate distinct orders per customer.

{ INCLUDE [Customer Name] : COUNTD([Order ID]) }

Question 4: Calculate sales per state within the current view's dimensions.

{ INCLUDE [State] : SUM([Sales]) }

Question 5: Calculate sales at the customer-product level.

{ INCLUDE [Customer Name], [Product Name] : SUM([Sales]) }

Question 6: How do you display average sales per customer by region?

Answer: Create { INCLUDE [Customer Name] : SUM([Sales]) }, place Region in the view, and set the resulting measure's aggregation to AVG.

Question 7: Why can INCLUDE results change when dimensions are added to or removed from a view?

Answer: INCLUDE uses the view's dimensions plus the declared dimensions. Changing the view dimensions can therefore change the calculation's effective level of detail and the resulting aggregation.

Question 8: Do ordinary dimension filters affect INCLUDE expressions?

Answer: Yes. Dimension filters are evaluated before INCLUDE and EXCLUDE calculations in Tableau's order of operations.

20. Tableau INCLUDE Expressions Interview Questions and Answers

Q1. What is an INCLUDE expression in Tableau?

An INCLUDE expression is an LOD calculation that adds specified dimensions to the view's existing level of detail for the calculation.

Q2. What is the syntax of INCLUDE?

{ INCLUDE [Dimension] : AGGREGATE([Measure]) }

Q3. What is the main purpose of INCLUDE?

It calculates values at a finer level of detail than the visualization may display and allows those values to be aggregated back to the view level.

Q4. Can INCLUDE contain multiple dimensions?

Yes. Multiple dimensions can be declared, separated by commas.

Q5. How is INCLUDE different from FIXED?

INCLUDE uses the dimensions in the view plus the declared dimensions. FIXED uses its explicitly declared dimensions independently of the view's dimensions.

Q6. How is INCLUDE different from EXCLUDE?

INCLUDE adds dimensions to the view's level of detail for the calculation, while EXCLUDE removes specified dimensions from the view's level of detail.

Q7. Why is AVG often used with an INCLUDE expression?

AVG can average detailed subtotals, such as customer sales totals, to display an average at the higher level shown in the visualization.

Q8. Can INCLUDE be used for date calculations?

Yes. Date expressions such as DATETRUNC('day', [Order Date]) can be used as declared dimensions.

Q9. Do INCLUDE calculations respond to dimension filters?

Generally, yes. Ordinary dimension filters are evaluated before INCLUDE calculations.

Q10. When should you use INCLUDE?

Use INCLUDE when the analysis requires a finer calculation level than the current view without requiring every additional dimension to be displayed in the visualization.

21. Quick Revision Checklist

• Understand the purpose of INCLUDE expressions.
• Remember the syntax: { INCLUDE [Dimension] : AGGREGATE([Measure]) }.
• Know that INCLUDE adds dimensions to the view's level of detail for the calculation.
• Understand how customer-level totals can be averaged by region.
• Know how to use multiple dimensions in an INCLUDE expression.
• Understand how the view's dimensions affect the calculation.
• Know the difference between INCLUDE, FIXED, and EXCLUDE.
• Remember that dimension filters generally affect INCLUDE calculations.
• Choose the final aggregation based on the business question.
• Practice customer sales, product profit, order counts, and daily sales examples.

22. Official Learning Resources

• Tableau Help: INCLUDE Level of Detail Expressions
• Tableau Help: How Level of Detail Expressions Work
• Tableau Help: Filters and LOD Expressions
• Tableau Whitepaper: Understanding LOD Expressions

Conclusion

INCLUDE expressions are useful when you need to calculate at a finer level of detail than the visualization displays. By adding dimensions to the view's calculation level, they let you calculate customer totals, product totals, state-level sales, distinct order counts, and other detailed metrics before summarizing the results. For accurate analysis, understand the view's granularity, choose the right aggregation, and check how filters affect the calculation.

Continue learning with JustAcademy Tableau Training or register for a Tableau course demo.